/*
	select * FROM location
	select * FROM INVENTORY_LOT
	select * FROM lot_table
*/
ALTER         proc sp_load_final_inventory_by_partnumber_and_lineitem_new
( @partNumber varchar(50), @lineItem_id int, @cslit_id int, @vendorAddressId int, @substituteProcessStepIds varchar(500) )
As
declare @process_step_id bigint
declare @process_type varchar(20)
declare @process_type_name varchar(20)
declare @results Table(	Lot_Number varchar(50),
			Fab_lot_Number VARCHAR(50),
			Lot_ID bigint, 
			Location_ID int,
			Location varchar(20),
			Lot_Date_Code varchar(50),
			Step_Name varchar(20),
			Date_In datetime,
			Qty_In int,
			Balance_Qty int,
			Reserved_Qty int,
			Quantity_Pull int,
			Lot_Process_Step_ID bigint,
			Lot_Part_Number varchar(50),
			Lot_Entered_By bit,
			Lot_Manual_Hold bit)

--declare @temp_cslit_id int
declare @temp_lot_id int
declare @temp_process_step_id bigint
declare @temp_location_id int
declare @temp_lot_number varchar(50)
declare @temp_fab_lot_Number varchar(50)
declare @temp_location varchar(20)
declare @temp_lot_date_code varchar(50)
declare @temp_step_name varchar(20)
declare @temp_date_in datetime
declare @temp_qty_in int
declare @temp_bal_qty int
declare @temp_ship_qty int
declare @temp_qty_pull int
declare @tempCursor CURSOR
declare @pfcursor_deallocate_status int
--declare @partNumber varchar(50)
declare @reserved_quantity int
declare @max_time_stamp datetime
declare @temp_product_type varchar(50)
declare @parent_product_id int 

declare @temp_lot_process_step_id bigint
declare @temp_lot_part_number varchar(50)
declare @temp_lot_entered_by bit
declare @temp_Lot_Manual_Hold bit
declare @substitute_processsteps_id_table Table(ProcessStep_Id int)
declare @is_sample_sales_order bit

set @is_sample_sales_order = 1

select @is_sample_sales_order = SO_Is_Sample from Sales_Order SO, Customer_Open_Order_LineItem COOL WHERE SO.SalesOrder_ID = COOL.SalesOrder_ID and LineItem_ID = @lineItem_id

set @pfcursor_deallocate_status = 0
select @temp_product_type = isnull(p_type,'CHIP') from product where Part_Number =  @partNumber and p_marketing_Part_Number is not null	
if( @temp_product_type = 'CHIP' )
begin
	select @temp_process_step_id = ProcessStep_ID from Product where Part_Number =  @partNumber and p_marketing_Part_Number is not null
end
else
begin
	select @parent_product_id = P_Parent_Product_ID  from product where Part_Number = @partNumber and p_marketing_Part_Number is not null
	
	if( @parent_product_id = -1)
	begin	
		select @temp_process_step_id = ProcessStep_ID from Product where Part_Number =  @partNumber and p_marketing_Part_Number is not null
		--Select Substitute_Product_ProcessStep_Id from Product_Substitutables_Table where Product_ProcessStep_Id = @temp_process_step_id
	end
	else		
	begin
		select @temp_process_step_id = processStep_ID from product where Product_id = ( select P_Parent_Product_ID from product where Part_Number = @partNumber and p_marketing_Part_Number is not null )
	end
end	
set @process_type_name = 'Finished Goods'

IF ( @substituteProcessStepIds IS NULL OR @substituteProcessStepIds = '' )
BEGIN
	SET @substituteProcessStepIds = @temp_process_step_id + ''
END 

insert into @substitute_processsteps_id_table( ProcessStep_Id )  select ProcessStep_Id from fn_get_process_steps_in_string( @substituteProcessStepIds )

if( @is_sample_sales_order = 0 )
begin
	set @tempCursor = CURSOR for
	select Lot_ID, Lot_Number, Fab_lot_Number, Location_ID, Lot_Date_Code, ProcessStep_ID, ISNULL(Lot_Entered_By,0), ISNULL( Lot_Manual_Hold, 1 ), Qty_In,  Date_In from Lot_table where Lot_Id in ( select lt.Lot_ID from Lot_Table  LT, Inventory_Lot IL where LT.Lot_Id = IL.Lot_Id and LT.Qty_In > 0 and ( Lot_Category = 'PROD' or Lot_Category = 'ENGG' ) and ( ProcessStep_ID in ( Select ProcessStep_Id From @substitute_processsteps_id_table ) ) and location_id = (select Location_id from location where address_id = @vendorAddressId and L_type = 'Finished Goods') and ( Lot_Current_Status = 'Running')  and ( Lot_Manual_Hold <> 0 ) UNION all select Lot_ID from Lot_Table where lot_id in ( select lot_id from customer_shipping_lot_number_table where cslit_id = @cslit_id and CSLNT_Qty_Pull > 0 ) ) order by location_id
end
else
begin
	set @tempCursor = CURSOR for
	select Lot_ID, Lot_Number, Fab_lot_Number, Location_ID, Lot_Date_Code, ProcessStep_ID, ISNULL(Lot_Entered_By,0), ISNULL( Lot_Manual_Hold, 1 ), Qty_In,  Date_In from Lot_table where Lot_Id in ( select lt.Lot_ID from Lot_Table  LT, Inventory_Lot IL where LT.Lot_Id = IL.Lot_Id and LT.Qty_In > 0 and ( Lot_Category = 'PROD' ) and ( ProcessStep_ID in ( Select ProcessStep_Id From @substitute_processsteps_id_table ) ) and location_id = (select Location_id from location where address_id = @vendorAddressId and L_type = 'Finished Goods') and ( Lot_Current_Status = 'Running')  and ( Lot_Manual_Hold <> 0 ) UNION all select Lot_ID from Lot_Table where lot_id in ( select lot_id from customer_shipping_lot_number_table where cslit_id = @cslit_id and CSLNT_Qty_Pull > 0 ) ) order by location_id
end
		
open @tempCursor
fetch next from @tempCursor
	into @temp_lot_id, @temp_lot_number, @temp_fab_lot_Number, @temp_location_id, @temp_lot_date_Code, @temp_lot_process_step_id, @temp_lot_entered_by, @temp_Lot_Manual_Hold, @temp_qty_in, @temp_date_in 
while @@fetch_status = 0
begin
	set @temp_lot_part_number = null

	Select @temp_lot_part_number=Part_Number From Product Where ProcessStep_ID=@temp_lot_process_step_id
		
	select @temp_bal_qty = IL_Qty_Balance from Inventory_Lot where Lot_ID = @temp_lot_id
	
	if @temp_bal_qty is null
	begin
		set @temp_bal_qty = @temp_qty_in
	end

	set @temp_qty_pull = -1
	select @temp_qty_pull = CSLNT_Qty_Pull from customer_shipping_lot_number_table cslnt where cslit_id = @cslit_id and Lot_ID = @temp_lot_id

	select @temp_location = (select v.V_Name + '/'+ a.City from Location l, Vendor v, Address a where l.Location_ID = @temp_location_id and l.Vendor_ID = v.Vendor_ID and l.Address_ID = a.Address_ID)

	set @reserved_quantity = 0
	select @reserved_quantity = lrt_lot_quantity from lot_reservation_table lrt, customer_open_order_lineitem cool where cool.lineItem_id = @lineItem_id and cool.cool_line_item_no = lrt.lrt_line_item_no and cool.salesOrder_Id = lrt.lrt_salesOrder_id and lrt.lrt_lot_number = @temp_lot_number

	if @temp_qty_in = -1
	begin
		update @results set lot_id = -1 where lot_id = -1
	end
	else
	begin
		insert into @results( Lot_Number, Fab_lot_Number, Lot_ID, Location_ID, Location, Lot_Date_Code, Step_Name, Date_In, Qty_In, Balance_Qty, Reserved_Qty, Quantity_Pull, Lot_Process_Step_ID, Lot_Part_Number, Lot_Entered_By, Lot_Manual_Hold ) values( @temp_lot_number, @temp_fab_lot_Number, @temp_lot_id, @temp_location_id, @temp_location, @temp_lot_date_code, @process_type_name, @temp_date_in, @temp_qty_in, @temp_bal_qty, @reserved_quantity, @temp_qty_pull, @temp_lot_process_step_id, @temp_lot_part_number, @temp_lot_entered_by, @temp_Lot_Manual_Hold)
	end
	fetch next from @tempCursor
	into @temp_lot_id, @temp_lot_number, @temp_fab_lot_Number, @temp_location_id, @temp_lot_date_Code, @temp_lot_process_step_id, @temp_lot_entered_by, @temp_Lot_Manual_Hold, @temp_qty_in, @temp_date_in
end
close @tempCursor 
deallocate @tempCursor
select * from @results
return
